
const { validateFields } = require('../public/util.js')
const { successMsg, errorMsg,page,roomState } = require('../public/constant.js')
const {executeTransaction}=require('../mysql/public.js')
const pool = require('../mysql/mysql.js');
const express = require('express');
const router = express.Router();
const multer = require('multer');
const upload = multer();

const tableName='guest'

const getField=(tableName,fieldList)=>{
  var field=''
  for(var i in fieldList){
    field+=`${tableName}.${fieldList[i]},`
  }
  field=field.slice(0,-1)
  return field
}
let roomTypeFieldList=['room_type_name','room_type_price','bed_num','description']
var roomTypeField=getField('room_type',roomTypeFieldList)
let roomStateFieldList=['room_state_name']
var roomStateField=getField('room_state',roomStateFieldList)
let roomFieldList=['room_number','room_type_id','room_state_id']
var roomField=getField('room',roomFieldList)
let commonSql=`SELECT ${tableName}.*,${roomTypeField},${roomStateField},${roomField} FROM ${tableName}
left join room on ${tableName}.room_id=room.room_id 
left join room_state on room.room_state_id=room_state.room_state_id
left join room_type on room.room_type_id=room_type.room_type_id
`

// 添加
router.post('/Add', upload.none(), validateFields([
  { field: 'guestName', noEmpty: true, validType: 'string' },
  { field: 'idCard', noEmpty: true, validType: 'string' },
  { field: 'phone', noEmpty: true, validType: 'string' },
  { field: 'resideDate', noEmpty: true, validType: 'string' },
  { field: 'checkoutDate', validType: 'string' },
  { field: 'roomId', noEmpty: true, validType: 'number' },
  { field: 'deposit', noEmpty: true, validType: 'number' },
  { field: 'guestNum', noEmpty: true, validType: 'number' },
]), (req, res) => {
  const { guestName,idCard,phone,resideDate,checkoutDate,roomId,deposit,guestNum } = req.body;
  executeTransaction({pool,res,queries:[
    {
      sql:`SELECT * FROM ${tableName} WHERE id_card=? and is_delete=0`,
      params:[idCard],
      stopFlag:(result)=>result.length,
      stopEvent:()=>{res.status(200).send(errorMsg('该顾客已存在'))},
      successEvent:()=>{}
    },
    {
      sql:`SELECT * FROM room WHERE room_state_id=? and room_id=?`,
      params:[roomState.empty,roomId],
      stopFlag:(result)=>!result.length,
      stopEvent:()=>{res.status(200).send(errorMsg('该房间不存在或已入住'))},
      successEvent:()=>{}
    },
    {
      sql:`update room SET room_state_id=? WHERE room_id=?`,
      params:[roomState.busy,roomId],
    },
    {
      sql:`INSERT INTO ${tableName} (guest_name,id_card,phone,reside_date,checkout_date,room_id,deposit,guest_num) VALUES (${'?,'.repeat(8).slice(0,-1)})`,
      params:[guestName,idCard,phone,resideDate,checkoutDate,roomId,deposit,guestNum],
    },
    {
      sql:`${commonSql} where guest_id=?`,
      params:[],
      modifyParamsEvent:(params,result)=>{
        return [result.insertId]
      }
    },
    {
      sql: `INSERT INTO order_list (order_id,guest_id,deposit,price,room_type_name,room_type_id,room_type_price,
        room_description,bed_num,room_number,room_id) VALUES (${'?,'.repeat(11).slice(0,-1)})`,
      params:[],
      modifyParamsEvent:(params,result)=>{
        var orderNumber = 'SD' + 
        new Date().toISOString().replace(/[^0-9]/g, "") + 
        Math.floor(Math.random() * 10000).toString().padStart(4, '0');
        var {guest_id:guestId,room_type_price:roomPrice,room_type_name,room_type_id,room_type_price,
          description,bed_num,room_number,room_id}=result[0]
        var timestamp1 = new Date(resideDate).getTime();
        var timestamp2 = new Date(checkoutDate).getTime();
        var differenceInMilliseconds = Math.abs(timestamp2 - timestamp1); // 计算两个时间戳的差值
        var differenceInDays = differenceInMilliseconds / (1000 * 60 * 60 * 24); // 将差值转换为天数
        var price=roomPrice*differenceInDays
        return [orderNumber,guestId,deposit,price,room_type_name,room_type_id,room_type_price,
          description,bed_num,room_number,room_id]
      },
      successEvent:()=>{res.status(200).send(successMsg())}
    }
  ]})

});
// 修改
router.post('/Edit', upload.none(), validateFields([
  { field: 'guestId', noEmpty: true, validType: 'string' },
  { field: 'guestName', noEmpty: true, validType: 'string' },
  { field: 'idCard', noEmpty: true, validType: 'string' },
  { field: 'phone', noEmpty: true, validType: 'string' },
  { field: 'resideDate', noEmpty: true, validType: 'string' },
  { field: 'checkoutDate', validType: 'string' },
  { field: 'roomId', noEmpty: true, validType: 'number' },
  { field: 'deposit', noEmpty: true, validType: 'number' },
  { field: 'guestNum', noEmpty: true, validType: 'number' },
]), (req, res) => {
  const { guestId,guestName,idCard,phone,resideDate,checkoutDate,roomId,deposit,guestNum } = req.body;
  executeTransaction({pool,res,queries:[
    {
      sql:`SELECT * FROM ${tableName} WHERE guest_id=? and is_delete=0`,
      params:[guestId],
      stopFlag:(result)=>!result.length,
      stopEvent:()=>{res.status(200).send(errorMsg('没有该顾客'))},
      successEvent:()=>{},
    },
    {
      sql:`SELECT * FROM ${tableName} WHERE room_id=? and guest_id not in (?)`,
      params:[roomId,guestId],
      stopFlag:(result)=>!!result.length,
      stopEvent:()=>{res.status(200).send(errorMsg('房间号重复'))},
      successEvent:()=>{},
    },
    {
      sql:`UPDATE ${tableName} SET guest_name=?,id_card=?,phone=?,reside_date=?,checkout_date=?,room_id=?,deposit=?,guest_num=? WHERE guest_id=?`,
      params:[guestName,idCard,phone,resideDate,checkoutDate,roomId,deposit,guestNum,guestId],
      successEvent:()=>{res.status(200).send(successMsg())},
    },
  ]})
});



// 查询数据
router.get('/List', (req, res) => {
  pool.query(`${commonSql} where ${tableName}.is_delete=0 ORDER BY ${tableName}.create_time DESC`, (err, result) => {
    if (err){
      return res.status(500).send(errorMsg('数据库错误'));
    }
    var r = [...result]
    res.status(200).send(successMsg(r));
  });
});
// 查询数据
router.post('/PageList',upload.none(), (req, res) => {
  let {pageSize,pageIndex,roomStateId,roomTypeId}=req.body
  pageSize=pageSize||10
  pageIndex=pageIndex||1
  let conditionSql=''
  const arr=[pageSize,(pageIndex-1)*pageSize]
  const conditionArr=[]
  if(roomStateId){
    conditionSql+=` and ${tableName}.room_state_id=?`
    conditionArr.push(roomStateId)
    arr.push(roomStateId)
  }
  if(roomTypeId){
    conditionSql+=` and ${tableName}.room_type_id=?`
    conditionArr.push(roomTypeId)
    arr.push(roomTypeId)
  }
  const sql=`${commonSql} 
  WHERE ${tableName}.is_delete = 0${conditionSql} ORDER BY ${tableName}.create_time DESC LIMIT ? OFFSET ?; 
  SELECT COUNT(*) FROM ${tableName} WHERE is_delete = 0${conditionSql}`
  pool.query(sql,[...conditionArr,...arr],(err, result) => {
    if (err){
      console.log(err)
      return res.status(500).send(errorMsg('数据库错误'));
    }
    res.status(200).send(successMsg(page({data:result[0],total:result[1][0]['COUNT(*)'],pageIndex})));
  });
});
// 获取某一个
router.post('/GetOne', upload.none(), validateFields([
  { field: 'guestId', noEmpty: true, validType: 'number' },
]), (req, res) => {
  const { guestId } = req.body
  const sql = `${commonSql} WHERE ${tableName}.guest_id=? and ${tableName}.is_delete=0`
  pool.query(sql, [guestId], (err, result) => {
    if (err){
      return res.status(500).send(errorMsg('数据库错误'));
    }
    var r = [...result]
    res.status(200).send(successMsg(r))
  })
});
// 删除
router.get('/Delete', (req, res) => {
  const { guestId } = req.query
  if (!guestId) {
      return res.status(200).send(errorMsg('缺少参数guestId'));
  }
  executeTransaction({pool,res,queries:[
    {
      sql:`SELECT * FROM ${tableName} WHERE guest_id=?`,
      params:[guestId],
      stopFlag:(result)=>!result.length,
      stopEvent:()=>{res.status(200).send(errorMsg('无效id'))},
      successEvent:()=>{}
    },
    {
      sql:`UPDATE ${tableName} SET is_delete=1 WHERE guest_id=?`,
      params:[guestId],
      stopFlag:false,
      successEvent:()=>{res.status(200).send(successMsg('操作成功'))}
    }
  ]})
});
module.exports = router;